import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Properties;

/**
 * 
 * @author Krzysztof Marjański
 * Klasa reprezentująca połączenie z bazą danych SQL
 *
 */

public class DB {
	/**Pole przechowujące aktualnie nawiązane połączenie.*/
	private Connection con;
	/**
	 * Alokuje obiekt klasy DB, oraz ustawnawia połączenie z bazą.
	 */
	DB()
	{
		 Properties p = new Properties();
		 p.put("user","root");
		    p.put("password","root");
		      try {
				con = DriverManager.getConnection("jdbc:mysql://127.0.0.1/kalendarzyk?characterEncoding=UTF-8", p);
			} catch (SQLException e) {
				System.out.println("blad połączenia z bazą");
				e.printStackTrace();
			}
	}
	/**
	 * Pobiera wszystkie wydarzenia z bazy oraz zwraca ArrayList zawierający pobrane wydarzenia.
	 * @return Obiekt klasy ArrayList z pobranymi wydarzeniami.
	 */
	public  ArrayList<Event> fromSQL ()
	{
		Statement stmt = null;
		ArrayList<Event> list = new ArrayList<Event>();
		
	    String query = "select DAY(eventStart) as 'start_day', MONTH(eventStart) as 'start_month', YEAR(eventStart) as 'start_year'," +
	    			   "HOUR (eventStart) as 'start_hour', MINUTE(eventStart) as 'start_minute'," +
	    		       "DAY(eventEnd) as 'end_day', MONTH(eventEnd) as 'end_month', YEAR(eventEnd) as 'end_year'" +
	    		       ",HOUR (eventEnd) as 'end_hour', MINUTE(eventEnd) as 'end_minute'," +
	    		       "name,location,category,description,priority,remind,reminded from events;";
	    try {
	      stmt = con.createStatement();
	      ResultSet rs = stmt.executeQuery(query);
	      while (rs.next())
	      {
	    	  Event event = new Event ();
	    	  event.setEventStart(rs.getInt("start_year"), rs.getInt("start_month")-1, rs.getInt("start_day"), rs.getInt("start_hour"), rs.getInt("start_minute"));
	    	  event.setEventEnd(rs.getInt("end_year"), rs.getInt("end_month")-1, rs.getInt("end_day"), rs.getInt("end_hour"), rs.getInt("end_minute"));
	    	  event.setName(rs.getString("name"));
	    	  event.setLocation(rs.getString("location"));
	    	  event.setDescription(rs.getString("description"));
	    	  event.setPriority(rs.getInt("priority"));
	    	  event.setReminded(rs.getBoolean("reminded"));
	    	  list.add(event);  
	      }
	    } catch (SQLException e ) {
	      System.out.println("blad: "+e.getMessage());
	    }
	    return list;
	}
	/**
	 * Wykonuje zrzut wszystkich wydarzeń do bazy danych.
	 * @param events Obiekt klasy ArrayList zawierający wydarzenia do zapisania w bazie danych.
	 */
	public void toSQL (ArrayList<Event> events)
	{
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			stmt.execute("truncate table events");
		} catch (SQLException e1) {
			System.out.println("blad: "+e1.getMessage());
			e1.printStackTrace();
		}
	    try {
	    	stmt = con.createStatement();
	    	for (int i = 0 ; i<events.size() ; i++)
	    	{	
	    		Calendar startDate=events.get(i).getEventStart(), endDate=events.get(i).getEventEnd();
	    		String sSDate = new String (String.valueOf(startDate.get(Calendar.YEAR))+"-"+String.valueOf(startDate.get(Calendar.MONTH)+1)+
	    				"-"+String.valueOf(startDate.get(Calendar.DAY_OF_MONTH))+" "+String.valueOf(startDate.get(Calendar.HOUR_OF_DAY))+
	    				":"+String.valueOf(startDate.get(Calendar.MINUTE)));
	    		String sEDate = new String (String.valueOf(endDate.get(Calendar.YEAR))+"-"+String.valueOf(endDate.get(Calendar.MONTH)+1)+
	    				"-"+String.valueOf(endDate.get(Calendar.DAY_OF_MONTH))+" "+String.valueOf(endDate.get(Calendar.HOUR_OF_DAY))+
	    				":"+String.valueOf(endDate.get(Calendar.MINUTE)));
	    		String query = new String ("INSERT INTO events VALUES ('"+sSDate+"','"+sEDate+"','"+events.get(i).getName()+"','"+events.get(i).getLocation()+
	    				"',' ','"+events.get(i).getDescription()+"','"+events.get(i).getPriority()+"','"+events.get(i).getRemind()+
	    				"',"+events.get(i).isReminded())+");";
	    		stmt.executeUpdate(query);
	    		
	    	}
	    } catch (SQLException e) {
	    	System.out.println("blad: "+e.getMessage());
	      }
	}
	
	/**
	 * Pobiera z bazy solenizantów podanego dnia.
	 * @param date Data o której będą zdobyte informacje.
	 * @return Obiekt klasy String zawierający solenizantów.
	 */
	
	public String getNameDay (Calendar date)
	{
		 	Statement stmt = null;
		 	String   nameDay, month, day;
		 	month=String.valueOf(date.get(Calendar.MONTH)+1);
		 	day=String.valueOf(date.get(Calendar.DATE));
		    String query = "select imieniny from dayInfo where data = '0000-"+month+"-"+day+"'";
		    try {
		      stmt = con.createStatement();
		      ResultSet rs = stmt.executeQuery(query);
		      rs.next();
		      nameDay = rs.getString("imieniny");
		    } catch (SQLException e ) {
		    	nameDay = "";
		      System.out.println("blad: "+e.getMessage());
		    }
		    return nameDay;
	}
	/**
	 * Pobiera Godzinę zachodu słońca z bazy Danych.
	 * @param date Data o której będą zdobyte informacje.
	 * @return Obiekt klasy String Zawierający godzinę zachodu słońca.
	 */
	public String getSunset (Calendar date)
	{
	 	Statement stmt = null;
	 	String   sunset, month, day;
	 	month=String.valueOf(date.get(Calendar.MONTH)+1);
	 	day=String.valueOf(date.get(Calendar.DATE));
	    String query = "select sunset from dayInfo where data = '0000-"+month+"-"+day+"'";
	    try {
	      stmt = con.createStatement();
	      ResultSet rs = stmt.executeQuery(query);
	      rs.next();
	      sunset = rs.getString("sunset");
	    } catch (SQLException e ) {
	    	sunset = "";
	      System.out.println("blad: "+e.getMessage());
	    }
	    return sunset;
}
	/**
	 * Pobiera z bazy godzinę wchodu słońca.
	 * @param date Data o której będą zdobyte informacje.
	 * @return Obiekt klasy String zawierający godzinę wschodu słońca.
	 */
	public String getSunrise (Calendar date)
	{
	 	Statement stmt = null;
	 	String   sunrise, month, day;
	 	month=String.valueOf(date.get(Calendar.MONTH)+1);
	 	day=String.valueOf(date.get(Calendar.DATE));
	    String query = "select sunRise from dayInfo where data = '0000-"+month+"-"+day+"'";
	    try {
	      stmt = con.createStatement();
	      ResultSet rs = stmt.executeQuery(query);
	      rs.next();
	      sunrise = rs.getString("sunRise");
	    } catch (SQLException e ) {
	    	sunrise = "";
	      System.out.println("blad: "+e.getMessage());
	    }
	    return sunrise;
}

}
